package user_client;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

/*
 * import library.user_creator.CreatorReturnedInformationPane;
 */
/**
 *
 * @author Pawel Rychly
 *
 */
public class LibrarianUserClient extends DataBaseClient {

    public LibrarianUserClient(Connection connection, String userName,
            String userPassword) {

        super(connection, userName, userPassword);
        // TODO Auto-generated constructor stub
    }

    private boolean checkUser(String name, String surname, String address)
            throws SQLException {
        Statement stmt = this.getConnection().createStatement();

        ResultSet res = stmt.executeQuery("select count(*) as num from "
                + "czytelnicy where ( replace(lower(imie),' ','')"
                + " = replace(lower('" + name + "'),' ', ''))"
                + " and ( replace(lower(nazwisko),' ','') "
                + " = replace(lower('" + surname + "'),' ', ''))"
                + " and ( replace(lower(adres),' ','') "
                + " = replace(lower('" + address + "'),' ', ''))");

        res.next();
        Integer num = res.getInt("num");
        System.out.println("LibrarianUserClient: checking");
        if (num > 0) {
            return true;
        }

        return false;
    }

    /**
     * Add new user to database;
     *
     * @throws SQLException
     */
    /*
     * public boolean addClient(CreatorReturnedInformationPane pane, String
     * name, String surname, String address) throws SQLException { // check does
     * the client exists. // this.getConnection().setAutoCommit(false); try { if
     * (!checkUser(name, surname, address)) { System.out.println("l-1");
     * Statement stmt = this.getConnection().createStatement(); // get the user
     * id System.out.println("l-3");
     *
     * //ResultSet res = stmt.executeQuery(ALTER SEQUENCE ) ResultSet res =
     * stmt .executeQuery("select id_czytelnika_seq.NEXTVAL as ID from dual");
     * res.next(); Integer user_id = res.getInt("ID");
     * System.out.println("l-4"); // add user to table res =
     * stmt.executeQuery("insert into czytelnicy	values(" +
     * "id_czytelnika_seq.CURRVAL,'" + name + "', '" + surname + "','" +
     * address+ "')");
     *
     * // create user stmt.executeQuery("CREATE USER " + surname + "_" +
     * user_id.toString() + " IDENTIFIED BY " + "pass" + user_id.intValue() + "
     * DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp " + "QUOTA unlimited
     * ON users QUOTA unlimited ON " + "system PROFILE " + "default ");
     *
     * stmt.executeQuery("grant create session, select any table," + " insert
     * any table to " + surname + "_" + user_id.toString());
     *
     * stmt.executeQuery("grant UserClient to " + surname + "_" +
     * user_id.toString());
     *
     * stmt.close(); res.close(); this.getConnection().commit();
     * pane.getTextPane().getField("login").setText( surname + "_" +
     * user_id.toString()); pane.getTextPane().getField("temporary
     * password").setText( "pass" + user_id.intValue()); pane.setVisible(true);
     * System.out.println("utworzono użytkownika"); return true; } } catch
     * (SQLException e) { System.out.println("SQLError"); e.printStackTrace();
     * this.getConnection().rollback(); }
     *
     */
    // add user privileges
		/*
     * stmt.executeQuery("grant " + user_id + "IDENTIFIED BY" + password +
     * "DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp " + "QUOTA unlimited
     * ON users + QUOTA unlimited ON system PROFILE " + "default ");
     */
    /*
     * return false; }
     */
    /**
     * Removes user from system.
     */
    public void removeClient() {
    }

    /**
     * Accept borrowing book from library.
     */
    public void approveBorrowingBook() {
    }

    /**
     * Sends to database information about returning book.
     */
    public void noteReturningOfBook() {
    }

    /**
     * Adds book to database.
     */
    public void addBook() {
    }

    /**
     * Removes book from database.
     */
    public void removeBook() {
    }

    /**
     * Adds new section
     */
    public void addNewSection() {
    }

    /**
     * Removes section
     */
    public void removeSection() {
    }

    public Integer addEdition(String isbn, String nameOfEdition,
            String dateOfEdition, String title, String idOfDepartment)
            throws SQLException {
        ArrayList<String> cols = new ArrayList<String>();
        cols.add("isbn");
        cols.add("wydawnictwo");
        cols.add("data_wydania");
        cols.add("tytul");
        cols.add("id_dzialu");
        ArrayList<String> values = new ArrayList<String>();
        values.add(isbn);
        values.add(nameOfEdition);
        values.add(dateOfEdition);
        values.add(title);
        values.add(idOfDepartment);
        if (isExists(cols, values, "dzialy")) {
            return -1;
        } else {
            Statement stmt = this.getConnection().createStatement();
        /*    ResultSet res = stmt.executeQuery("select id_dzialu_seq.NEXTVAL as val from dual");
            res.next();
            Integer result = res.getInt("val");
         */
            int val = stmt.executeUpdate("INSERT INTO wydania VALUES ("
                    + "" +isbn+ ", '" + nameOfEdition 
                    + "', to_date('"+ dateOfEdition + "', 'yyyy/mm/dd'), '" +
                    title + "', " + idOfDepartment +" )");
            this.getConnection().commit();
            stmt.close();
            System.out.println("INDEX:" + val);
        }

        return 1;
    }

    /**
     * Ads new department
     */
    public Integer addDepartment(String name, String num) throws SQLException {
        ArrayList<String> cols = new ArrayList<String>();
        cols.add("nazwa");
        cols.add("nr_regalu");
        ArrayList<String> values = new ArrayList<String>();
        values.add(name);
        values.add(num);

        if (isExists(cols, values, "dzialy")) {
            return -1;
        } else {
            Statement stmt = this.getConnection().createStatement();
            ResultSet res = stmt.executeQuery(
                    "select id_dzialu_seq.NEXTVAL as val from dual");
            res.next();
            Integer result = res.getInt("val");
            int val = stmt.executeUpdate("INSERT INTO dzialy VALUES ("
                    + result + " , '" + name + "', " + num + " )");
            this.getConnection().commit();
            stmt.close();
            System.out.println("INDEX:" + val);
            return result;
        }


    }
    
    
       public Integer addAuthor(
               String name, String surname) throws SQLException {
        ArrayList<String> cols = new ArrayList<String>();
        cols.add("imie");
        cols.add("nazwisko");
        ArrayList<String> values = new ArrayList<String>();
        values.add(name);
        values.add(surname);

        if (isExists(cols, values, "autorzy")) {
            return -1;
        } else {
            Statement stmt = this.getConnection().createStatement();
            ResultSet res = stmt.executeQuery(
                    "select id_autora_seq.NEXTVAL as val from dual");
            res.next();
            Integer result = res.getInt("val");
            int val = stmt.executeUpdate("INSERT INTO autorzy VALUES ( "
                    + result +", '" + surname + "', '" + name + "' )");
            this.getConnection().commit();
            stmt.close();
            System.out.println("INDEX:" + val);
            return result;
        }


    }
}
